package com.betterjr.modules.risk.loanmanage.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;

import com.betterjr.common.annotation.BetterjrMapper;
import com.betterjr.mapper.common.Mapper;
import com.betterjr.modules.risk.loanmanage.entity.LoanData;

@BetterjrMapper
public interface LoanDataMapper extends Mapper<LoanData> {
    @Select("<script>" + "Select * FROM (SELECT "
            + "ld.ID id,ld.P_CONTRACT_ID contractId,ld.C_CONTRACT_NAME contractName,ld.C_CONTRACT_NO contractNo,ld.N_SUPPLIER_ID supplierId,ld.C_SUPPLIER_NAME supplierName,ld.C_SUPPLIER_NO supplierNo,ld.N_CORE_COMPANY_ID coreCompanyId,ld.C_CORE_COMPANY_NAME coreCompanyName,ld.C_FUND_PROVIDER_NAME fundProviderName,ld.N_FUND_PROVIDER_ID fundProviderId,ld.D_CONTRACT_SIGNING_DATE contractSigningDate,ld.N_LOAN_AMOUNT loanAmount,ld.D_LOAN_DATE loanDate,ld.C_STATUS STATUS,ld.D_CREATE_DATE createDate,ld.C_CREATOR creator,ld.N_CREATOR_ID creatorId,ld.D_UPDATE_DATE updateDate,ld.C_UPDATE_CREATOR updateCreator,ld.N_UPDATE_CREATOR_ID updateCreatorId"
            + ", MAX(CASE rd.C_REPAYMENT_TYPE WHEN '1' THEN rd.N_AMOUNT ELSE 0 END) 'repaymentPrincipal', MAX(CASE rd.C_REPAYMENT_TYPE WHEN '1' THEN rd.D_REPAYMENT_DATE ELSE 0 END) 'repaymentPrincipalDate', MAX(CASE rd.C_REPAYMENT_TYPE WHEN '0' THEN rd.N_AMOUNT ELSE 0 END) 'repaymentInterest', MAX(CASE rd.C_REPAYMENT_TYPE WHEN '0' THEN rd.D_REPAYMENT_DATE ELSE 0 END) 'repaymentInterestDate' FROM t_loan_data ld LEFT JOIN T_REPAYMENT_DETAILS rd ON rd.P_LOAN_DATA_ID=ld.ID WHERE 1=1 GROUP BY ld.ID ,ld.P_CONTRACT_ID ,ld.C_CONTRACT_NAME ,ld.C_CONTRACT_NO ,ld.N_SUPPLIER_ID ,ld.C_SUPPLIER_NAME ,ld.C_SUPPLIER_NO ,ld.N_CORE_COMPANY_ID ,ld.C_CORE_COMPANY_NAME ,ld.C_FUND_PROVIDER_NAME ,ld.N_FUND_PROVIDER_ID ,ld.D_CONTRACT_SIGNING_DATE ,ld.N_LOAN_AMOUNT ,ld.D_LOAN_DATE ,ld.C_STATUS ,ld.D_CREATE_DATE ,ld.C_CREATOR ,ld.N_CREATOR_ID ,ld.D_UPDATE_DATE ,ld.C_UPDATE_CREATOR ,ld.N_UPDATE_CREATOR_ID"
            + "<if test='parameter.id != null'> and ld.ID=#{parameter.id}</if>"
            + "<if test='parameter.supplierId != null'> and ld.N_SUPPLIER_ID=#{parameter.supplierId}</if>"
            + "<if test='parameter.supplierNo != null'> and ld.C_SUPPLIER_NO=#{parameter.supplierNo}</if>"
            + "<if test='parameter.supplierName != null'> and ld.C_SUPPLIER_NAME LIKE concat('%',#{parameter.supplierName},'%')</if>"
            + "<if test='parameter.coreCompanyId != null'> and ld.N_CORE_COMPANY_ID=#{parameter.coreCompanyId}</if>"
            + "<if test='parameter.fundProviderId != null'> and ld.N_FUND_PROVIDER_ID=#{parameter.fundProviderId}</if>"
            + "<if test='parameter.startDate != null'> and ld.D_LOAN_DATE &gt;= #{parameter.startDate}</if>"
            + "<if test='parameter.endDate != null'> and ld.D_LOAN_DATE &lt;= #{parameter.endDate}</if> "
            + "order by ld.D_CREATE_DATE desc) dt  limit #{startNum},#{endNum}" + "</script>")
    @ResultType(List.class)
    public List<LoanData> queryLoanDatasByPage(@Param("parameter") LoanData parameter,
            @Param("startNum") Integer startNum, @Param("endNum") Integer endNum);

    @Select("<script>" + "SELECT count(1) FROM t_loan_data ld  WHERE 1=1 "
            + "<if test='parameter.id != null'> and ld.ID=#{parameter.id}</if>"
            + "<if test='parameter.supplierId != null'> and ld.N_SUPPLIER_ID=#{parameter.supplierId}</if>"
            + "<if test='parameter.supplierNo != null'> and ld.C_SUPPLIER_NO=#{parameter.supplierNo}</if>"
            + "<if test='parameter.supplierName != null'> and ld.C_SUPPLIER_NAME LIKE concat('%',#{parameter.supplierName},'%')</if>"
            + "<if test='parameter.coreCompanyId != null'> and ld.N_CORE_COMPANY_ID=#{parameter.coreCompanyId}</if>"
            + "<if test='parameter.fundProviderId != null'> and ld.N_FUND_PROVIDER_ID=#{parameter.fundProviderId}</if>"
            + "<if test='parameter.startDate != null'> and ld.D_LOAN_DATE &gt;= #{parameter.startDate}</if>"
            + "<if test='parameter.endDate != null'> and ld.D_LOAN_DATE &lt;= #{parameter.endDate}</if>" + "</script>")
    @ResultType(Long.class)
    public Long queryLoanDatasCount(@Param("parameter") LoanData parameter);
}